require '../db_help/db_help'
require 'taobao_api'
#16 1625  50008165    50006842
def set_cat_info(item,cat,parent)
    while p = parent.fetch_hash do
      item[cat+'_id'] = p['t_cid']
      item[cat+'_name'] = p['cat_name']
    end
end
def get_category(dao,cid)
    ah={}
    categories = TaobaoAPI.get_with(:method =>'taobao.itemcats.get',
                                :fields=>'cid,parent_cid,name,is_parent,sort_order',
                                :cids=>cid)
    if categories['error_response']
        puts categories['error_response']
    else
        item_cats = categories['itemcats_get_response']['item_cats']['item_cat']
        item_cats.each do |it|
            ah[it[0].to_s]=it[1].to_s              
        end
        if ah.keys.length>0
              return if dao.query("select id from tao_categories where t_cid=#{ah['cid']}").num_rows>0
              #create record
              ah['t_cid']=ah['cid']
              ah['cat_name'] = ah['name']
              ah['parent_id']=ah['parent_cid']
              ah['status']='1'
              ah['cat_level']= ah['is_parent'].to_s=='true'||ah['parent_cid'].to_s=='16' ? 0 : 1
              ah['is_parent']= ah['is_parent'].to_s=='true' ? 1 : 0
              ah.delete('cid')
              ah.delete('name')
              ah.delete('parent_cid')
              ah['table']='tao_categories'
              ah['view_name']=ah['cat_name']
              ah['created_at']=TaobaoAPI.timestamp
              ah['updated_at']=TaobaoAPI.timestamp
              dao.query(dao.build_sql(ah))
              puts ah['t_cid']
              puts ah['parent_id']
              puts ah['is_parent']
              puts ah['cat_name']
              puts "*****************************************"
              get_item_prop_values(dao,ah['t_cid']) if ah['is_parent']==0
              get_sub_category(dao,ah['t_cid']) if ah['is_parent']==1
        end    
        
    end                             
end

def get_sub_category(dao,cid)
    categories = TaobaoAPI.get_with(:method =>'taobao.itemcats.get',
                                :fields=>'cid,parent_cid,name,is_parent,sort_order',
                                :parent_cid=>cid)
    if categories['error_response']
        puts categories['error_response']
    else
        item_cats = categories['itemcats_get_response']['item_cats']['item_cat']
        item_cats.each do |it|
            next if dao.query("select id from tao_categories where t_cid=#{it['cid']}").num_rows>0
          #create record
          it['t_cid']=it['cid']
          it['cat_name'] = it['name']
          it['parent_id']=it['parent_cid']
          it['status']='1'
          it['cat_level']= it['is_parent'].to_s=='true'||it['parent_cid'].to_s=='16' ? 0 : 1
          it['is_parent']= it['is_parent'].to_s=='true' ? 1 : 0
          it.delete('cid')
          it.delete('name')
          it.delete('parent_cid')
          it['table']='tao_categories'
          it['view_name']=it['cat_name']
          it['created_at']=TaobaoAPI.timestamp
          it['updated_at']=TaobaoAPI.timestamp
          dao.query(dao.build_sql(it))
          puts it['t_cid']
          puts it['parent_id']
          puts it['is_parent']
          puts it['cat_name']
          puts "*****************************************"
          sleep 1.5
          get_item_prop_values(dao,it['t_cid']) if it['is_parent']==0
          get_category(dao,it['t_cid']) if it['is_parent']==1
        end
    end                             
end

#获取属性值
def get_item_prop_values(dao,cid)
    begin
    #loop category
      props = TaobaoAPI.get_with(:method=>'taobao.itemprops.get',
                       :fields=>'pid,name,parent_pid,parent_vid,prop_values,sort_order',
                       :cid=>cid)
     if props['error_response']
         puts props['error_response']
     elsif !props['itemprops_get_response'].nil?
        prop = props['itemprops_get_response']['item_props']['item_prop'] unless props['itemprops_get_response']['item_props'].nil?
        unless prop.nil?
          #loop property
              prop.each do |p|
                 propvalues=p['prop_values']['prop_value'] unless p['prop_values'].nil?
                 unless propvalues.nil?
                   #loop prop values
                   propvalues.each do |pv|
                     if pv.class.to_s=='Hash'
                       pv['table']='tao_property_values'
                       pv['cid']=cid
                       pv['pid']=p['pid']
                       pv['property_name']=p['name']
                       pv['sort_order']=0
                       pv['created_at']=TaobaoAPI.timestamp
                       pv['updated_at']=TaobaoAPI.timestamp
                       dao.query(dao.build_sql(pv))
                     end                      
                   end#pv.each
              end#unless.nil 
              p['table']='tao_item_properties'
              p['created_at']=TaobaoAPI.timestamp
              p['updated_at']=TaobaoAPI.timestamp
              p.delete('prop_values')
              
              #puts dao.build_sql(p)
              dao.query(dao.build_sql(p))             
            end#p.each
        end
    end#else
  rescue Exception=>e
    raise e
  end
end#get_item_prop_values

db_config1 = {"host" => '118.126.2.138', 'encoding' => 'utf8', 'username' => 'root', 'password' => 'admin', 'database' => 'tao_hidoudou'}
db_config2 = {"host" => '118.126.2.138', 'encoding' => 'utf8', 'username' => 'root', 'password' => 'admin', 'database' => 'hidoudou_staging'}
dao = DBHelp::DBHelp.out_initialize(db_config1)
dao2 = DBHelp::DBHelp.out_initialize(db_config2)
def update_cat1_cat2(dao)
#rs = dao.query("select num_iid,nick,id from tao_items where original_cid is null and status>=0 order by id desc")
rs = dao.query("select num_iid,nick,id,original_cid from tao_items where status>=0 and (cat1_id=0 or cat1_id is null) ")
rs.each do |row|
    sleep 0.5
    items = TaobaoAPI.get_with(:method=>'taobao.item.get',
                              :fields=>'cid',
                              :nick=>row[1],
                              :num_iid=>row[0])
    item = items['item_get_response']['item'] unless items['item_get_response'].nil?
    if !item.nil?
         cats = dao.query("select cat_name,t_cid,cat_level,parent_id from tao_categories where t_cid=#{item['cid']} limit 1")
         if cats.num_rows==0
            get_category(dao,row[3])
         end

          while category = cats.fetch_hash do
            if category['cat_level']=='0'
              item['cat1_id'] = category['t_cid']
              item['cat1_name'] = category['cat_name']
            elsif category['cat_level']=='1'
              parent = dao.query("select cat_name,t_cid,cat_level,parent_id from tao_categories where t_cid=#{category['parent_id']}")
              set_cat_info(item,'cat1',parent)
              item['cat2_id'] = category['t_cid']
              item['cat2_name'] = category['cat_name']
            end
          end
          puts row[2]
          begin
             sql =  "update tao_items set created_at='#{TaobaoAPI.timestamp}',updated_at='#{TaobaoAPI.timestamp}',original_cid='#{item['cid']}',cat1_id='#{item['cat1_id']}',cat1_name='#{item['cat1_name']}'"
             if !item['cat2_id'].nil?
                 sql = sql + ",cat2_id='#{item['cat2_id']}',cat2_name='#{item['cat2_name']}'"
             end
             sql = sql + " where id=#{row[2]}"
            dao.query(sql)
          rescue Exception => e
            puts e
          end
    else
        if items['error_response']['sub_code'].to_s=='isv.invalid-permission:get-item'
            dao.query("update tao_items set status=-2 where id=#{row[2]}")
        elsif items['error_response']['sub_code'].to_s=='isv.item-is-delete:invalid-numIid-or-iid'
            dao.query("update tao_items set status=-1 where id=#{row[2]}")
        end
    end

end unless rs.nil?
end
def get_values(dao)
    db_config1 = {"host" => 'localhost', 'encoding' => 'utf8', 'username' => 'root', 'password' => 'admin', 'database' => 'tao'}
    dao = DBHelp::DBHelp.out_initialize(db_config1)
    get_item_prop_values(dao,50011404)
end


def get(dao,cid)
    categories = TaobaoAPI.get_with(:method =>'taobao.itemcats.get',
                                :fields=>'cid,parent_cid,name,is_parent,sort_order',
                                :cids=>cid)
    if categories['error_response']
        puts categories['error_response']
    else
        it = categories['itemcats_get_response']['item_cats']['item_cat']
        puts 'cid - '+it['cid']
        puts 'parent_cid - '+it['parent_cid']
        puts 'name - '+it['name']
        puts 'is parent - '+it['is_parent']
        puts "****************************"
        get(dao,it['parent_cid']) unless it['parent_cid'].to_s=='0'|| it['parent_cid'].to_s=='16'
        if it['parent_cid'].to_s=='0'
           get_category(dao,it['cid'])
        end
    end
    
end

def update_staging_cat1_cat2(dao1,dao2)
    cat1_is_null = dao1.query("select name,id from items where cat1_id is null")
    cat1_is_null.each do |row|
        rs = dao2.query("select cat1_name,cat2_name,tc.is_parent from tao_items ti join tao_categories tc on ti.cat1_id=tc.t_cid where format_title='#{row[0]}' limit 1")
        item = {}
        next if rs.nil?
        detail=rs.fetch_row
        next if detail.nil?
        if !detail[0].nil? && detail[0].to_s.strip!=''
               rs_cid=dao1.query("select id from item_categories where name='#{detail[0]}'")
               if !rs_cid.nil? && rs_cid.num_rows>0
                  cat1_id = rs_cid.fetch_row[0]
               else
                   cat1 = {'table'=>'item_categories','name'=>detail[0],'is_parent'=>detail[2],'status'=>0,'flag'=>0,'created_at'=>TaobaoAPI.timestamp,'updated_at'=>TaobaoAPI.timestamp}
                     dao1.query(dao1.build_sql(cat1))
                     cat1_id = dao1.query("select @@IDENTITY").fetch_row[0] 
               end
               item['cat1_id']=cat1_id
               item['cat1_name']=detail[0]
               rs_cid.free
           end
           if !detail[1].nil? && detail[1].to_s.strip!=''
               rs_cid=dao1.query("select id from item_categories where name='#{detail[1]}'")
                  if !rs_cid.nil? && rs_cid.num_rows>0
                     cat2_id = rs_cid.fetch_row[0]
                  else
                      cat2 = {'table'=>'item_categories','name'=>detail[0],'status'=>0,'flag'=>0,'created_at'=>TaobaoAPI.timestamp,'updated_at'=>TaobaoAPI.timestamp}
                       dao1.query(dao1.build_sql(cat2))
                       cat2_id = dao1.query("select @@IDENTITY").fetch_row[0] 
                  end
                  item['cat2_id']=cat1_id
                  item['cat2_name']=detail[1]
                  rs_cid.free
           end
           sql = "update items set cat1_id=#{item['cat1_id']},cat1_name='#{item['cat1_name']}' "
           if !item['cat2_id'].nil?
               sql = sql+ ", cat2_id=#{item['cat2_id']},cat2_name='#{item['cat2_name']}'"
           end
           sql = sql + " where id=#{row[1]}"
           dao1.query(sql)
    end
end
=begin    
db_config1 = {"host" => 'localhost', 'encoding' => 'utf8', 'username' => 'root', 'password' => 'admin', 'database' => 'tao'}
dao = DBHelp::DBHelp.out_initialize(db_config1)
rs = dao.query("select distinct cid from tmptable")
rs.each do |row|
   get(row[0]) 
end
=end
#get(dao,50003811)
#update_cat1_cat2(dao)
#get_category(dao,50006842)
#get_values(dao)      
#get_item_prop_values(dao,50003811)
update_staging_cat1_cat2(dao2,dao)


